package com.onemore.task.model;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.voovan.db.JdbcOperate;
import org.voovan.tools.log.Logger;

import com.alibaba.fastjson.JSONObject;
import com.onemore.task.JdbcGlobal;
import com.onemore.task.OneConfig;

public class ModelB2CWMS extends JdbcGlobal{
	final static private String fields[] = new String[] {"SyncBillId","BILLOUTDATE","QTYIN","SL"};

	static public boolean isNotExist()
	{
		return validateTableExist("ONE_B2C_WMS");
	}
	static public boolean isNotExist(String tableName)
	{
		return validateTableExist(tableName);
	}
    static public void createTable() throws SQLException
    {
    	String sql = "CREATE TABLE [dbo].[ONE_B2C_WMS] (\r\n" + 
    			"[BillId] varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL DEFAULT '' ,\r\n" + 
    			"[SyncBillId] varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL DEFAULT '' ,\r\n" + 
    			"[billdate] char(30) COLLATE Chinese_PRC_CI_AS NOT NULL DEFAULT '' ,\r\n" + 
    			"[QTYIN] int NOT NULL DEFAULT ((0)) ,\r\n" + 
    			")";
//		"PRIMARY KEY ([BillId])\r\n" + 
    	getJdbcOperate().update(sql);
    }
    static public void arrayToTable(List<Map<String, Object>> listWms,String tableName) throws SQLException
	{
		JdbcOperate operate = getJdbcOperate();
		operate.update("truncate table "+tableName);
		for(Map<String, Object> map:listWms) {
			String BillId = (String)map.get("BillId");
			String SyncBillId = (String)map.get("SyncBillId");
			String billdate = map.get("billdate").toString();
			Integer QTYIN = (Integer)map.get("QTYIN");
			operate.update("INSERT INTO "+tableName+" (BillId,SyncBillId,billdate,QTYIN)  values('"+BillId+"','"+SyncBillId+"','"+billdate+"','"+QTYIN+"')");
		}
	}
	static public void importWmsTable() throws SQLException
	{
		arrayToTable(getList(),"ONE_B2C_WMS");
	}
    static public List<Map<String, Object>> getList() throws SQLException
    {
    	String filterDate = "";
    	String str="";
		String starDate = null;
		String endDate = null;
		Properties properties= OneConfig.getProperties();
		if(properties != null) {
			if(starDate == null) {
				str = OneConfig.getProperties().getProperty("starDate");
				if(str != null) {
					starDate = str;
				}else {
					//只展示一周数据
					//starDate = "dateadd(day,-7,getdate())";
					starDate = "2018-01-01";
				}
			}
		}
    	String sql = "SELECT a.BillId, a.order_sn AS SyncBillId, a.ModifyDate AS billdate, aaa.qty AS QTYIN FROM TBillOrderInfo a, ( SELECT so_sn, SUM(goods_number) AS qty FROM TSyn_order_goods GROUP BY so_sn ) aaa WHERE a.shipping_status = '8' AND a.status = '6' AND a.ModifyDate > '"+starDate+"' AND a.BillId = aaa.so_sn UNION SELECT a.BillId, a.SyncBillId, a.BillInDate AS billdate, aaa.qty AS QTYIN FROM TBillStockPurchase a, ( SELECT BillId, SUM(QTYIN) AS qty FROM TBillStockPurchaseDet GROUP BY BillId ) aaa WHERE a.billtype = '2' AND a.status = '130' AND a.BillInDate > '"+starDate+"' AND a.BillId = aaa.BillId UNION SELECT A.BillId, A.SyncBillId, A.billdate, SUM(B.QtyOut) AS QTYIN FROM TBillNoteBatchOut C, TBillBatchOut A, TBillBatchOutDet B WHERE A.BillId = B.BillId AND A.OrigBillId = c.BillId AND A.billdate > '"+starDate+"' AND A.BillType = '1' AND C.Status = '198' AND A.SyncBillId LIKE 'PF%' GROUP BY A.BillId, A.SyncBillId, A.billdate UNION SELECT a.BillId, a.SyncBillId, a.BillInDate AS billdate, aaa.qty AS QTYIN FROM TBillStockPurchase a, ( SELECT BillId, SUM(QTYIN) AS qty FROM TBillStockPurchaseDet GROUP BY BillId ) aaa WHERE a.billtype = '2' AND a.status = '130' AND a.BillInDate > '"+starDate+"' AND a.BillId = aaa.BillId";
        List<Map<String, Object>> manyMaps = null;
		manyMaps = getWmsOperate().queryMapList(sql);
		return manyMaps;
    }
}
